CREATE OR REPLACE Procedure Usp_Sync_Incident As
Begin
  declare 
  idCount number(20);
  begin

       for e in (select Id,Incident_Number from tb_incident_temp for update) loop
         select count(*) into idCount from tb_incident a where a.incident_number= e.incident_number;
         if idCount>0 then
           Update Tb_Incident A
            Set(
              Event_Source,Category,Subcategory,Business_System_Type,Busynesssystemid,Description,Detailed_Decription,
              IMPACT,URGENCY,PRIORITY,EMPLOYEE_NUMBER,CUSTOMER_DEPARTMENT,CUSTOMER_EMAIL,CUSTOMER_NAME,CUSTOMER_PHONE,ASSIGNED_GROUP,
              Assigned_Group_Id,Assignee,Assignee_Login_Id,Resolution,Resolve_Code,Resolved_By,Resolved_Date,Status,Submitter,Create_Date,
              Last_Modified_By,Modified_Date,Closed_By,Closed_Date,ALERT_ID,CLOSEDBYFULLNAME,INSTANCEID,LASTMODIFYBYFULLNAME,PERSON_ID,
			  RESOLVEDBYFULLNAME,RETURN_FLAG,SUBMITTERFULLNAME,Update_Date
            )=
            (
              Select
              Event_Source,Category,Subcategory,Business_System_Type,Busynesssystemid,Description,Detailed_Decription,
              IMPACT,URGENCY,PRIORITY,EMPLOYEE_NUMBER,CUSTOMER_DEPARTMENT,CUSTOMER_EMAIL,CUSTOMER_NAME,CUSTOMER_PHONE,ASSIGNED_GROUP,
              Assigned_Group_Id,Assignee,Assignee_Login_Id,Resolution,Resolve_Code,Resolved_By,Resolved_Date,Status,Submitter,Create_Date,
              Last_Modified_By,Modified_Date,Closed_By,Closed_Date,ALERT_ID,CLOSEDBYFULLNAME,INSTANCEID,LASTMODIFYBYFULLNAME,PERSON_ID,
			  RESOLVEDBYFULLNAME,RETURN_FLAG,SUBMITTERFULLNAME,sysdate
              From Tb_Incident_Temp B
              where b.id=e.id
            )
            where a.incident_number=e.incident_number;
         else
           Insert Into Tb_Incident
            ( Incident_Number,Event_Source,Category,Subcategory,Business_System_Type,Busynesssystemid,Description,Detailed_Decription,
              IMPACT,URGENCY,PRIORITY,EMPLOYEE_NUMBER,CUSTOMER_DEPARTMENT,CUSTOMER_EMAIL,CUSTOMER_NAME,CUSTOMER_PHONE,ASSIGNED_GROUP,
              Assigned_Group_Id,Assignee,Assignee_Login_Id,Resolution,Resolve_Code,Resolved_By,Resolved_Date,Status,Submitter,Create_Date,
              LAST_MODIFIED_BY,MODIFIED_DATE,CLOSED_BY,CLOSED_DATE,ALERT_ID,CLOSEDBYFULLNAME,INSTANCEID,LASTMODIFYBYFULLNAME,PERSON_ID,
			  RESOLVEDBYFULLNAME,RETURN_FLAG,SUBMITTERFULLNAME,Sync_Date,Update_Date
            )
            Select
              Incident_Number,Event_Source,Category,Subcategory,Business_System_Type,Busynesssystemid,Description,Detailed_Decription,
              IMPACT,URGENCY,PRIORITY,EMPLOYEE_NUMBER,CUSTOMER_DEPARTMENT,CUSTOMER_EMAIL,CUSTOMER_NAME,CUSTOMER_PHONE,ASSIGNED_GROUP,
              Assigned_Group_Id,Assignee,Assignee_Login_Id,Resolution,Resolve_Code,Resolved_By,Resolved_Date,Status,Submitter,Create_Date,
              Last_Modified_By,Modified_Date,Closed_By,Closed_Date,ALERT_ID,CLOSEDBYFULLNAME,INSTANCEID,LASTMODIFYBYFULLNAME,PERSON_ID,
			  RESOLVEDBYFULLNAME,RETURN_FLAG,SUBMITTERFULLNAME,sysdate,sysdate
            From Tb_Incident_Temp A
            where a.id=e.id and rownum=1;
         end if;
         
         delete from tb_incident_temp where id=e.id;
       end loop;
  end;
  Commit;
  Exception
      When Others Then
      Rollback;
      usp_insert_synclog( P_LINE_NUMBER => SQLERRM, P_ERR_CODE => SQLCODE, P_ERR_MESSAGE => 'USP_SYNC_INCIDENT error' );


END USP_SYNC_INCIDENT;
/
